﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Manufacturing
{
    public class MBRManager:ConnectionManager
    {
        public SqlException AddOrder(MBRUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO OMBR (DocNum, ProductionType, ProductCode, ProductDescription, BatchNum, OrderQty, ServedQty, UnitMeasure,DocStatus,DateNeeded,WhseCode,WhseName,ProductStructure,DateCreated,CreatedBy,DateModified,ModifiedBy) VALUES " +
                                                            "(@DocNum, @ProductionType, @ProductCode, @ProductDescription, @BatchNum, @OrderQty, @ServedQty, @UnitMeasure, @DocStatus, @DateNeeded, @WhseCode, @WhseName, @ProductStructure, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@ProductionType", oUnit.ProductionType));
                oCommand.Parameters.Add(new SqlParameter("@ProductCode", oUnit.ProductCode));
                oCommand.Parameters.Add(new SqlParameter("@ProductDescription", oUnit.ProductDescription));
                oCommand.Parameters.Add(new SqlParameter("@BatchNum", oUnit.BatchNumber));
                oCommand.Parameters.Add(new SqlParameter("@OrderQty", oUnit.QtyOrder));
                oCommand.Parameters.Add(new SqlParameter("@ServedQty", oUnit.QtyServed));
                oCommand.Parameters.Add(new SqlParameter("@UnitMeasure", oUnit.UoM));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateNeeded", oUnit.DateNeeded));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", oUnit.WarehouseCode));
                oCommand.Parameters.Add(new SqlParameter("@WhseName", oUnit.WarehouseName));
                oCommand.Parameters.Add(new SqlParameter("@ProductStructure", oUnit.ProductStructure));

                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));



                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.MOLineItems.Rows)
                {
                    SqlCommand oLineCommand = new SqlCommand();
                    oLineCommand.Connection = this.Connection;


                    oLineCommand.CommandText = "INSERT INTO MBR1 (DocNum, ItemGroup, WhseCode, WHseName, ItemCode, Dscription, BaseQty, QtyRequirement, ActualQty,  UnitCost) VALUES (@DocNum, @ItemGroup, @WhseCode, @WHseName, @ItemCode, @Dscription, @BaseQty, @QtyRequirement, @ActualQty, @UnitCost)";
                    
                    oLineCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oLineCommand.Parameters.Add(new SqlParameter("@ItemGroup", oRow["ItemGroup"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@WhseCode", oRow["WhseCode"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@WhseName", oRow["WhseName"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@Dscription", oRow["Dscription"].ToString()));
                   
                    oLineCommand.Parameters.Add(new SqlParameter("@BaseQty", oRow["BaseQty"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@QtyRequirement", oRow["QtyRequirement"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@ActualQty", oRow["ActualQty"].ToString()));
                    
                    //oLineCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oLineCommand.Parameters.Add(new SqlParameter("@UnitCost", oRow["UnitCost"].ToString()));

                    oLineCommand.ExecuteNonQuery();
                }
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }

        public SqlException UpdateOrder(MBRUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OMBR SET " +
                                            "ProductionType=@ProductionType, " +
                                            "ProductCode=@ProductCode, " + 
                                            "ProductDescription=@ProductDescription, " +
                                            "BatchNum=@BatchNum, " + 
                                            "OrderQty=@OrderQty, " + 
                                            "ServedQty=@ServedQty, "+ 
                                            "UnitMeasure=@UnitMeasure, " + 
                                            "DocStatus=@DocStatus, " + 
                                            "DateNeeded=@DateNeeded, " +
                                            "WhseCode=@WhseCode, " + 
                                            "WhseName=@WhseName, " +
                                            "ProductStructure=@ProductStructure, " + 
                                            "DateModified=@DateModified, " + 
                                            "ModifiedBy=@ModifiedBy " + 
                                            "WHERE DocNum=@DocNum";

                
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@ProductionType", oUnit.ProductionType));
                oCommand.Parameters.Add(new SqlParameter("@ProductCode", oUnit.ProductCode));
                oCommand.Parameters.Add(new SqlParameter("@ProductDescription", oUnit.ProductDescription));
                oCommand.Parameters.Add(new SqlParameter("@BatchNum", oUnit.BatchNumber));
                oCommand.Parameters.Add(new SqlParameter("@OrderQty", oUnit.QtyOrder));
                oCommand.Parameters.Add(new SqlParameter("@ServedQty", oUnit.QtyServed));
                oCommand.Parameters.Add(new SqlParameter("@UnitMeasure", oUnit.UoM));
                oCommand.Parameters.Add(new SqlParameter("@DateNeeded", oUnit.DateNeeded));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@WhseCode", oUnit.WarehouseCode));
                oCommand.Parameters.Add(new SqlParameter("@WhseName", oUnit.WarehouseName));
                oCommand.Parameters.Add(new SqlParameter("@ProductStructure", oUnit.ProductStructure));

                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                SqlCommand oRemoveLineItems = new SqlCommand();

                oRemoveLineItems.Connection = this.Connection;
                oRemoveLineItems.CommandText = "DELETE FROM MBR1 WHERE DocNum=@DocNum";
                oRemoveLineItems.Parameters.Add(new SqlParameter("@DocNum",oUnit.DocumentNumber));

                oRemoveLineItems.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.MOLineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        SqlCommand oLineCommand = new SqlCommand();

                        oLineCommand.Connection = this.Connection;
                        oLineCommand.CommandText = "INSERT INTO MBR1 (DocNum, ItemGroup, WhseCode, WHseName, ItemCode, Dscription, BaseQty, QtyRequirement, ActualQty, UnitCost) VALUES (@DocNum, @ItemGroup, @WhseCode, @WHseName, @ItemCode, @Dscription, @BaseQty, @QtyRequirement, @ActualQty, @UnitCost)";

                        oLineCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oLineCommand.Parameters.Add(new SqlParameter("@ItemGroup", oRow["ItemGroup"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@WhseCode", oRow["WhseCode"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@WhseName", oRow["WhseName"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@Dscription", oRow["Dscription"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@BaseQty", oRow["BaseQty"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@QtyRequirement", oRow["QtyRequirement"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@ActualQty", oRow["ActualQty"].ToString()));
                        oLineCommand.Parameters.Add(new SqlParameter("@UnitCost", oRow["UnitCost"].ToString()));

                        oLineCommand.ExecuteNonQuery();
                    }
                }

                oUnit.MOLineItems.AcceptChanges();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }

        public DataTable GetManufacturingOrder(string DocNum)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OMBR WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
            

        }

        public DataTable GetMaterials(string DocNum)
        {
            DataTable dt = new DataTable();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM MBR1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseMaterialsByMONumber(string DocNum)
        {
            SqlConnection oConnection = new SqlConnection();
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            DocNum = "%" + DocNum + "%";

            oConnection.ConnectionString = this.ConnectionString;
            oConnection.Open();

            oCommand.Connection = oConnection;
            oCommand.CommandText = "SELECT * FROM OMBR WHERE DocNum LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));    

            oConnection.Close();


            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);
            return dt;
            
        }

        public DataTable BrowseMaterialsByProductCode(string ProductCode)
        {
            SqlConnection oConnection = new SqlConnection();
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            ProductCode = "%" + ProductCode + "%";

            oConnection.ConnectionString = this.ConnectionString;
            oConnection.Open();

            oCommand.Connection = oConnection;
            oCommand.CommandText = "SELECT * FROM OMBR WHERE ProductCode LIKE @ProductCode";
            oCommand.Parameters.Add(new SqlParameter("@ProductCode", ProductCode));

            oConnection.Close();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);
            return dt;

        }


        public DataTable BrowseMaterialsByProductName(string ProductName)
        {
            SqlConnection oConnection = new SqlConnection();
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            ProductName = "%" + ProductName + "%";

            oConnection.ConnectionString = this.ConnectionString;
            oConnection.Open();

            oCommand.Connection = oConnection;
            oCommand.CommandText = "SELECT * FROM OMBR WHERE ProductDescription LIKE @ProductDescription";
            oCommand.Parameters.Add(new SqlParameter("@ProductDescription", ProductName));

            oConnection.Close();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);
            return dt;

        }


        public void Onhold(string DocNum)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OMBR SET DocStatus='On-Hold' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            oCommand.ExecuteNonQuery();
        }

        public void Released(string DocNum)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OMBR SET DocStatus='Released' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            oCommand.ExecuteNonQuery();
        }

        public void InProcess(string DocNum)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OMBR SET DocStatus='In-Process' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            oCommand.ExecuteNonQuery();
        }

        public void Closed(string DocNum)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OMBR SET DocStatus='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            oCommand.ExecuteNonQuery();
        }
    }
}
